package com.easyjava.builder;

import com.easyjava.bean.Constants;
import com.easyjava.bean.FieldInfo;
import com.easyjava.bean.TableInfo;
import com.easyjava.utils.JsonUtils;
import com.easyjava.utils.MyStringUtils;
import com.easyjava.utils.PropertiesUtils;
import org.apache.commons.lang3.ArrayUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;


/**
 * @Author: 小闹心
 * @Description: 创建表的建造者模式
 * @DateTime: 2024/11/16 9:36
 **/
public class BuildTable {
    private static final Logger logger = LoggerFactory.getLogger(BuildTable.class);
    private static Connection conn = null;

    private static final String SQL_SHOW_TABLES_STATUS = "show table status";
    private static final String SQL_SHOW_TABLES_FIELDS = "show full fields from %s";
    private static final String SQL_SHOW_TABLES_INDEX = "show index from %s";

    static {
        String driverName = PropertiesUtils.getString("db.driver.name");
        String url = PropertiesUtils.getString("db.url");
        String username = PropertiesUtils.getString("db.username");
        String password = PropertiesUtils.getString("db.password");
        try {
            Class.forName(driverName);
            conn = DriverManager.getConnection(url,username,password);
        } catch (Exception e) {
            logger.error("数据库链接失败",e);
        }
    }

    public static List<TableInfo> getTables(){
        PreparedStatement ps = null;
        ResultSet tableResult = null;

        List<TableInfo> tableInfoList = new ArrayList<>();
        try{
            ps = conn.prepareStatement(SQL_SHOW_TABLES_STATUS);
            tableResult = ps.executeQuery();
            while (tableResult.next()){
                String tableName = tableResult.getString("name");
                String comment = tableResult.getString("comment");

                String beanName = tableName;
                if(Constants.IGNORE_TABLE_PREFIX){
                    beanName = beanName.substring(beanName.indexOf("_") + 1);
                }
                beanName = processFiled(beanName,true);
                TableInfo tableInfo = new TableInfo();
                tableInfo.setTableName(tableName);
                tableInfo.setBeanName(beanName);
                tableInfo.setComment(comment);
                tableInfo.setBeanParamName(beanName + Constants.SUFFIX_BEAN_QUERY);

                List<FieldInfo> fieldInfoList = readFieldInfo(tableInfo);
                tableInfo.setFieldList(fieldInfoList);
                getKeyIndexInfo(tableInfo);
                tableInfoList.add(tableInfo);
            }
        }catch (Exception e){
            logger.error("查询表失败",e);
        }finally {
            closePreparedAndResultSet(ps, tableResult);
            if(conn != null){
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return tableInfoList;
    }

    private static List<FieldInfo> readFieldInfo(TableInfo tableInfo){
        PreparedStatement ps = null;
        ResultSet fieldResult = null;
        List<FieldInfo> fieldInfoList = new ArrayList<>();
        List<FieldInfo> extendField = new ArrayList<>();
        List<FieldInfo> fieldEnumList = new ArrayList<>();
        try{
            ps = conn.prepareStatement(String.format(SQL_SHOW_TABLES_FIELDS,tableInfo.getTableName()));
            fieldResult = ps.executeQuery();
            while (fieldResult.next()){
                String field = fieldResult.getString("field");
                String type = fieldResult.getString("type");
                String extra = fieldResult.getString("extra");
                String comment = fieldResult.getString("comment");
                if(type.indexOf("(") > 0){
                    type = type.substring(0,type.indexOf("("));
                }
                String propertyName = processFiled(field,false);
                FieldInfo fieldInfo = new FieldInfo();
                fieldInfo.setFieldName(field);
                fieldInfo.setComment(comment);
                fieldInfo.setSqlType(type);
                fieldInfo.setAutoIncrement("auto_increment".equalsIgnoreCase(extra));
                fieldInfo.setPropertyName(propertyName);
                fieldInfo.setJavaType(processJavaType(type));

                if(ArrayUtils.contains(Constants.SQL_DATE_TYPES,type)){
                    tableInfo.setHaveDate(true);
                }
                if(ArrayUtils.contains(Constants.SQL_DATE_TIMES_TYPES,type)){
                    tableInfo.setHaveDateTime(true);
                }
                if(ArrayUtils.contains(Constants.SQL_DECIMAL_TYPES,type)){
                    tableInfo.setHaveBigDecimal(true);
                }

                // 判断是否有String类型
                if(ArrayUtils.contains(Constants.SQL_STRING_TYPES,fieldInfo.getSqlType())){
                    FieldInfo tempInfo = new FieldInfo();
                    tempInfo.setJavaType(fieldInfo.getJavaType());
                    tempInfo.setPropertyName(MyStringUtils.lowerCaseFirstLetter(fieldInfo.getPropertyName()) + Constants.SUFFIX_BEAN_QUERY_FUZZY);
                    tempInfo.setFieldName(fieldInfo.getFieldName());
                    tempInfo.setSqlType(fieldInfo.getSqlType());
                    extendField.add(tempInfo);
                }
                // 是否有日期类型
                if(ArrayUtils.contains(Constants.SQL_DATE_TIMES_TYPES,fieldInfo.getSqlType()) || ArrayUtils.contains(Constants.SQL_DATE_TYPES,fieldInfo.getSqlType())){
                    FieldInfo tempInfo = new FieldInfo();
                    tempInfo.setJavaType(fieldInfo.getJavaType());
                    tempInfo.setPropertyName(MyStringUtils.lowerCaseFirstLetter(fieldInfo.getPropertyName()) + Constants.SUFFIX_BEAN_QUERY_TIME_START);
                    tempInfo.setFieldName(fieldInfo.getFieldName());
                    tempInfo.setSqlType(fieldInfo.getSqlType());
                    extendField.add(tempInfo);
                    tempInfo = new FieldInfo();
                    tempInfo.setJavaType(fieldInfo.getJavaType());
                    tempInfo.setPropertyName(MyStringUtils.lowerCaseFirstLetter(fieldInfo.getPropertyName()) + Constants.SUFFIX_BEAN_QUERY_TIME_END);
                    tempInfo.setFieldName(fieldInfo.getFieldName());
                    tempInfo.setSqlType(fieldInfo.getSqlType());
                    extendField.add(tempInfo);
                }
                // 判断是否为枚举
                if(processEnum(fieldInfo.getComment())){
                    fieldEnumList.add(fieldInfo);
                }
                tableInfo.setFieldExtendList(extendField);
                fieldInfoList.add(fieldInfo);
                tableInfo.setFieldEnumList(fieldEnumList);
            }
        }catch (Exception e){
            logger.error("查询表失败",e);
        }finally {
            closePreparedAndResultSet(ps, fieldResult);
        }
        return fieldInfoList;
    }

    private static void getKeyIndexInfo(TableInfo tableInfo){
        PreparedStatement ps = null;
        ResultSet fieldResult = null;
        try{
            Map<String,FieldInfo> tempMap = new HashMap<>();
            for(FieldInfo fieldInfo : tableInfo.getFieldList()){
                tempMap.put(fieldInfo.getFieldName(),fieldInfo);
            }

            ps = conn.prepareStatement(String.format(SQL_SHOW_TABLES_INDEX,tableInfo.getTableName()));
            fieldResult = ps.executeQuery();
            while (fieldResult.next()){
                String keyName = fieldResult.getString("key_name");
                Integer nonUnique = fieldResult.getInt("non_unique");
                String columnName = fieldResult.getString("column_name");
                if(nonUnique == 1) continue;
                List<FieldInfo> keyFieldList = tableInfo.getKeyIndexMap().get(keyName);
                if(keyFieldList == null){
                    keyFieldList = new ArrayList<>();
                    tableInfo.getKeyIndexMap().put(keyName,keyFieldList);
                }
                keyFieldList.add(tempMap.get(columnName));
            }
        }catch (Exception e){
            logger.error("查询索引失败",e);
        }finally {
            closePreparedAndResultSet(ps, fieldResult);
        }
    }
    // 关闭数据库连接
    private static void closePreparedAndResultSet(PreparedStatement ps, ResultSet fieldResult) {
        if(fieldResult != null){
            try {
                fieldResult.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(ps != null){
            try {
                ps.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }


    private static String processFiled(String filed,Boolean upperCaseFirstLetter){
        StringBuffer sb = new StringBuffer();
        String[] fields = filed.split("_");
        sb.append(upperCaseFirstLetter ? MyStringUtils.upperCaseFirstLetter(fields[0]) : fields[0]);
        for(int i = 1,len = fields.length;i < len;i++){
            sb.append( MyStringUtils.upperCaseFirstLetter(fields[i]));
        }
        return sb.toString();
    }


    private static String processJavaType(String sqlType){
        if(ArrayUtils.contains(Constants.SQL_INTEGER_TYPES,sqlType)){
            return "Integer";
        }else if(ArrayUtils.contains(Constants.SQL_LONG_TYPES,sqlType)){
            return "Long";
        }else if(ArrayUtils.contains(Constants.SQL_STRING_TYPES,sqlType)){
            return "String";
        }else if(ArrayUtils.contains(Constants.SQL_DATE_TIMES_TYPES,sqlType) || ArrayUtils.contains(Constants.SQL_DATE_TYPES,sqlType)){
            return "Date";
        }else if(ArrayUtils.contains(Constants.SQL_DECIMAL_TYPES,sqlType)){
            return "BigDecimal";
        } else if(ArrayUtils.contains(Constants.SQL_INTEGER_UNSIGNED_TYPES,sqlType)){
            return "int";
        }
        else{
            throw new RuntimeException("不支持的SQL类型：" + sqlType);
        }
    }

    // 判断是否符合枚举类，根据注释看
    private static boolean processEnum(String comment){
        String reg = ".*1.*?" + Constants.DB_COMMENT_ENUM_INTERVAL + "2.*?";
        return comment.matches(reg);
    }
}
